package com.olap.starter.common.util;

import com.baomidou.mybatisplus.annotation.DbType;
import com.olap.starter.common.vo.ExecuteQuery;
import com.olap.starter.common.vo.Parameter;
import com.olap.starter.common.vo.QueryParameter;
import com.olap.starter.util.DateUtils;
import org.apache.commons.lang3.StringUtils;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

import static com.olap.starter.common.util.QueryGeneratorUtil.extractPlaceholders;

/**
 * @author WBG
 * @date 2022/11/14 10:49
 * @describe
 */
public class ExecuteQueryUtil {

    public static ExecuteQuery init(QueryParameter queryParameter) {
        return buildSql(new ExecuteQuery(), queryParameter);
    }

    private static Map<String, Object> parSqlStr(QueryParameter queryParameter) {
        List<String> keys = extractPlaceholders(queryParameter.getSql(), "#");
        Map<String, Object> map = new HashMap<>();
        for (String key : new HashSet<>(keys)) {
            String k2 = null;
            if (queryParameter.getQueryParameterListAnd() != null) {
                for (Parameter parameter : queryParameter.getQueryParameterListAnd()) {
                    if (parameter.getType() == 5) {
                        k2 = parameter.getKey().toUpperCase() + "_START";
                    } else if (parameter.getType() == 6) {
                        k2 = parameter.getKey().toUpperCase() + "_END";
                    } else {
                        k2 = parameter.getKey().toUpperCase();
                    }
                    if (key.toUpperCase().equals(k2) && parameter.getValue() != null) {
                        map.put(key, parameter.getValue());
                        parameter.setValue(null);
                    }
                }
            }
            if (map.get(key) == null) {
                throw new RuntimeException("查询必填参数[" + key + "]不能为空！");
            }
        }
        return map.isEmpty() ? null : map;
    }

    private static ExecuteQuery buildSql(ExecuteQuery executeQuery, QueryParameter queryParameter) {
        executeQuery.setNotHumpToLine(queryParameter.getNotHumpToLine());
        executeQuery.setHumpToLine(queryParameter.isHumpToLine());
        //先把转换时间给全部转换
        //parToDate(queryParameter);
        //对特殊字符处理  #{xxx}
        Map<String, Object> parSqlStrMap = parSqlStr(queryParameter);
        and(executeQuery, queryParameter.getQueryParameterListAnd(), queryParameter.getDbType());
        or(executeQuery, queryParameter.getQueryParameterListOr(), queryParameter.getDbType());
        in(executeQuery, queryParameter.getQueryParameterMapIn());
        wsMake(executeQuery, queryParameter);
        if (parSqlStrMap != null) {
            if (executeQuery.getAm() != null) {
                executeQuery.getAm().putAll(parSqlStrMap);
            } else {
                executeQuery.setAm(parSqlStrMap);
            }
        }
        return executeQuery;
    }

    private static void doBuildMyBatisSql(boolean isAnd, String key, Parameter p, String dbType, StringBuilder sql) {
        if ("1".equals(p.getToDate())) {
            if (DbType.ORACLE.getDb().equals(dbType)) {
                sql.append(" TO_DATE(");
            } else if (DbType.MYSQL.getDb().equals(dbType)) {
                sql.append(" STR_TO_DATE(");
            } else if (DbType.SQL_SERVER.getDb().equals(dbType)) {
                sql.append(" CONVERT(datetime, ");
            }
        }
        sql.append("#{ ").append(isAnd ? "am" : "om").append(".").append(key).append(" }");
        if ("1".equals(p.getToDate())) {
            String originalDate = p.getValue().toString();
            if (DbType.ORACLE.getDb().equals(dbType)) {
                String formatMask;
                if (originalDate.matches("^\\d{4}$")) {                    // 年份 2025
                    formatMask = "YYYY";
                } else if (originalDate.matches("^\\d{4}-\\d{2}$")) {      // 年月 2025-03
                    formatMask = "YYYY-MM";
                } else if (originalDate.matches("^\\d{4}-\\d{2}-\\d{2}$")) { // 日期 2025-03-12
                    formatMask = "YYYY-MM-DD";
                } else if (originalDate.matches("^\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}$")) { // 含时间
                    formatMask = "YYYY-MM-DD HH24:MI:SS";
                } else {
                    throw new IllegalArgumentException("Oracle不支持的日期格式: " + originalDate);
                }
                //dbExpression = String.format("TO_DATE('%s','%s')", originalDate, formatMask);
                sql.append(",'").append(formatMask).append("') ");
                // MySQL处理逻辑
            } else if (DbType.MYSQL.getDb().equals(dbType)) {
                String formatStr;
                if (originalDate.matches("^\\d{4}$")) {                     // 年份 2025
                    formatStr = "%Y";
                } else if (originalDate.matches("^\\d{4}-\\d{2}$")) {      // 年月 2025-03
                    formatStr = "%Y-%m";
                } else if (originalDate.matches("^\\d{4}-\\d{2}-\\d{2}$")) { // 日期
                    formatStr = "%Y-%m-%d";
                } else if (originalDate.matches("^\\d{4}-\\d{2}-\\d{2}  \\d{2}:\\d{2}:\\d{2}$")) {
                    formatStr = "%Y-%m-%d %H:%i:%s";
                } else {
                    throw new IllegalArgumentException("MySQL不支持的日期格式: " + originalDate);
                }
                //dbExpression = String.format("STR_TO_DATE('%s','%s')", originalDate, formatStr);
                sql.append(",'").append(formatStr).append("') ");

                // SQL Server处理逻辑 [4]()
            } else if (DbType.SQL_SERVER.getDb().equals(dbType)) {
                String formatCode;
                if (originalDate.matches("^\\d{4}$")) {                    // 年份 2025 → 补全为2025-01-01
                    formatCode = "23"; // yyyy-mm-dd格式
                } else if (originalDate.matches("^\\d{4}-\\d{2}$")) {      // 年月 2025-03 → 补全为2025-03-01
                    formatCode = "23"; // yyyy-mm-dd格式
                } else if (originalDate.matches("^\\d{4}-\\d{2}-\\d{2}$")) { // 日期 2025-03-12
                    formatCode = "23"; // yyyy-mm-dd格式
                } else if (originalDate.matches("^\\d{4}-\\d{2}-\\d{2}  \\d{2}:\\d{2}:\\d{2}$")) { // 含时间
                    formatCode = "120"; // yyyy-mm-dd hh:mi:ss格式
                } else {
                    throw new IllegalArgumentException("SQL Server不支持的日期格式: " + originalDate);
                }
                //dbExpression = String.format("CONVERT(datetime,  '%s', %s)", date, formatCode);
                sql.append(",").append(formatCode).append(") ");
                sql.append(formatCode);
            } else {
                throw new IllegalArgumentException("不支持的数据库类型: " + dbType);
            }
        }
    }

    private static void in(ExecuteQuery executeQuery, Map<String, Object> queryParameterMapIn) {
        if (queryParameterMapIn != null) {
            for (String s : queryParameterMapIn.keySet()) {
                if (queryParameterMapIn.get(s) instanceof Collection) {
                    StringBuilder stringBuilder = new StringBuilder();
                    Collection<Object> list = (Collection<Object>) queryParameterMapIn.get(s);
                    list.forEach(o -> {
                        if (stringBuilder.length() != 0) {
                            stringBuilder.append(",");
                        }
                        stringBuilder.append("'").append(o).append("'");
                    });
                    if (stringBuilder.length() != 0)
                        executeQuery.setInSql(s + " IN ( " + stringBuilder + " )");
                } else {
                    executeQuery.setInSql(s + " IN ( " + queryParameterMapIn.get(s) + " )");
                }
            }
        }
    }

    private static void or(ExecuteQuery executeQuery, List<Parameter> queryParameterListOr, String dbType) {
        if (queryParameterListOr != null && queryParameterListOr.size() > 0) {
            Map<String, Object> om = new HashMap<>();
            StringBuilder sql = new StringBuilder();
            for (int i = 0; i < queryParameterListOr.size(); i++) {
                Parameter p = queryParameterListOr.get(i);
                if (i != 0) {
                    sql.append(" OR ");
                }
                String key = isHumpToLine(executeQuery, p.getKey());
                String keys = key.replaceAll("\\.", "") + "_or_" + i;
                sql.append(key);
                buildMyBatisSql(p, sql, false, keys, dbType);
                //sql.append(key).append(whereDto.getTypeSql()).append("#{ ").append("whereOrKeyValueMap").append(".").append(keys).append(" }");
                om.put(keys, p.getValue());
            }
            sql.append(")");
            executeQuery.setOm(om);
            executeQuery.setOrSql("(" + sql);
        }
    }

    private static void and(ExecuteQuery executeQuery, List<Parameter> queryParameterListAnd, String dbType) {
        if (queryParameterListAnd != null && queryParameterListAnd.size() > 0) {
            initWhereKeyValueMap(executeQuery, queryParameterListAnd);
            StringBuilder as = buildSql(executeQuery, true, dbType);
            StringBuilder os = buildSql(executeQuery, false, dbType);
            if (as != null) {
                if (os != null) {
                    as.append(" AND ").append(os);
                }
                executeQuery.setAndSql(as.toString());
            } else if (os != null) {
                executeQuery.setAndSql(os.toString());
            }
        }
    }

    private static StringBuilder buildSql(ExecuteQuery executeQuery, boolean isAnd, String dbType) {
        if ((isAnd && executeQuery.getAml().size() == 0) || (!isAnd && executeQuery.getOml().size() == 0)) {
            return null;
        }
        Map<String, List<Parameter>> ml = isAnd ? executeQuery.getAml() : executeQuery.getOml();
        Map<String, Object> m = new HashMap<>();
        StringBuilder sql = new StringBuilder();
        for (String f : ml.keySet()) {
            if (sql.length() != 0) {
                sql.append(" AND ");
            }
            List<Parameter> l = ml.get(f);
            sql.append(" ( ");
            for (int i = 0; i < l.size(); i++) {
                if (i != 0) {
                    sql.append(isAnd ? " AND " : " OR ");
                }
                sql.append(f);
                String key = f.replaceAll("\\.", "") + i;
                buildMyBatisSql(l.get(i), sql, isAnd, key, dbType);
                m.put(key, l.get(i).getValue());
            }
            sql.append(" ) ");
        }
        if (isAnd) {
            executeQuery.setAm(m);
        } else {
            executeQuery.setOm(m);
        }
        return sql;
    }

    private static void buildMyBatisSql(Parameter p, StringBuilder sql, boolean isAnd, String key, String dbType) {
        if (p.getType() == 2) {
            sql.append(" != ");
        } else if (p.getType() == 3) {
            sql.append(" > ");
        } else if (p.getType() == 4) {
            sql.append(" < ");
        } else if (p.getType() == 5) {
            sql.append(" >= ");
        } else if (p.getType() == 6) {
            sql.append(" <= ");
        } else if (p.getType() >= 7 && p.getType() <= 9) {//789
            if (dbType != null && "oracle".equals(dbType.toLowerCase())) {
                sql.append(" like ");
                if (p.getType() == 7 || p.getType() == 8) {
                    sql.append("'%' || ");
                }
            } else {
                sql.append(" like CONCAT(");
                if (p.getType() == 7 || p.getType() == 8) {
                    sql.append("'%',");
                }
            }

        } else {
            sql.append(" = ");
        }
        doBuildMyBatisSql(isAnd, key, p, dbType, sql);

        if (p.getType() >= 7 && p.getType() <= 9) {
            if (dbType != null && "oracle".equals(dbType.toLowerCase())) {
                if (p.getType() > 7) {
                    sql.append(" || '%'");
                }
            } else {
                if (p.getType() > 7) {
                    sql.append(",'%'");
                }
                sql.append(")");
            }
        }
    }

    private static void wsMake(ExecuteQuery executeQuery,  QueryParameter queryParameter) {
        String executeWhere = queryParameter.getExecuteWhere();
        StringBuilder ws = new StringBuilder();
        boolean iaw = false;
        if (StringUtils.isNotBlank(executeWhere)) {
            ws.append(executeWhere);
            iaw = true;
        }
        if(StringUtils.isNotBlank(queryParameter.getAuthSql())){
            if(iaw){
                ws.append(" AND ");
            }
            ws.append(queryParameter.getAuthSql());
            iaw = true;
        }

        if (StringUtils.isNotBlank(executeQuery.getAndSql())) {
            if (iaw) {
                ws.append(" AND ");
            }
            ws.append(executeQuery.getAndSql());
            iaw = true;
        }
        if (StringUtils.isNotBlank(executeQuery.getInSql())) {
            if (iaw) {
                ws.append(" AND ");
            }
            ws.append(executeQuery.getInSql());
            iaw = true;
        }
        if (StringUtils.isNotBlank(executeQuery.getOrSql())) {
            if (iaw) {
                ws.append(" OR ");
            }
            ws.append(executeQuery.getOrSql());
        }
        executeQuery.setWs(ws);
    }

    private static void initWhereKeyValueMap(ExecuteQuery executeQuery, List<Parameter> queryParameterListAnd) {
        for (Parameter parameter : queryParameterListAnd) {
            String key = parameter.getKey();
            if (StringUtils.isNotBlank(key) && parameter.getValue() != null) {
                key = isHumpToLine(executeQuery, key);
                if (((executeQuery.getAml().containsKey(key) || executeQuery.getOml().containsKey(key)) && (parameter.getType() == 1 || parameter.getType() == 2 || parameter.getType() == 8))) {//or
                    List<Parameter> oml = executeQuery.getOml().getOrDefault(key, new ArrayList<>());
                    oml.add(parameter);
                    if (executeQuery.getAml().containsKey(key)) {
                        oml.addAll(executeQuery.getAml().get(key));
                        executeQuery.getAml().remove(key);
                    }
                    executeQuery.getOml().put(key, oml);
                } else {
                    List<Parameter> aml = executeQuery.getAml().getOrDefault(key, new ArrayList<>());
                    aml.add(parameter);
                    executeQuery.getAml().put(key, aml);
                }
            }
        }
    }

    private static String isHumpToLine(ExecuteQuery executeQuery, String key) {
        if (executeQuery.isHumpToLine()) {
            if (executeQuery.getNotHumpToLine() != null && executeQuery.getNotHumpToLine().size() != 0) {
                for (String s : executeQuery.getNotHumpToLine()) {
                    if (s.equals(key)) {
                        return key;
                    }
                }
            }
            return hump(key);
        }
        return key;
    }

    private static String hump(String str) {
        StringBuilder sb = new StringBuilder(str);
        int temp = 0;
        if (!str.contains("_")) {
            for (int i = 0; i < str.length(); i++) {
                if (Character.isUpperCase(str.charAt(i))) {
                    sb.insert(i + temp, "_");
                    temp += 1;
                }
            }
        }
        return sb.toString().toLowerCase();
    }


}
